﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class JobsiteCommandDtlsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public JobsiteCommandDtlsDAL()
        {

        }
        public DataSet LoadJobSiteCommandDtls(Nullable<int> Sequence, Nullable<int> JobSiteCommand)
        {
            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@JobSiteCommand", JobSiteCommand);

                strSQL = "SELECT d.*"
                        + ", j.JOB_SITE_NAME"
                        + ", CASE WHEN CANCEL = 1 THEN CAST('ยุบเลิก' AS CHAR) ELSE '' END AS CANCEL_TEXT"
                       // + " JOB_SITE_CMD_DTLS_SEQ, JOB_SITE_CMD_SEQ, JOB_SITE_SEQ"
                       //+ ", FROM_REF_NO, TO_REF_NO, FROM_JOB_SITE_NAME, TO_JOB_SITE_NAME"
                       //+ ", FROM_UPPER_SEQ, TO_UPPER_SEQ, FROM_UPPER_NAME, TO_UPPER_NAME"
                       //+ ", OLD_CMD_REF_NO, NEW_CMD_REF_NO, OLD_CMD_DATE, NEW_CMD_DATE"
                       //+ ", OLD_START_DATE"
                       //+ ", CANCEL, DATE_START, RECORD_STATUS"
                   + " FROM HRM_MP_JOB_SITE_COMMAND_DTLS d"
                   + " INNER JOIN HRM_MP_JOB_SITE_COMMANDS c ON d.JOB_SITE_CMD_SEQ = c.JOB_SITE_CMD_SEQ"
                   + " LEFT JOIN HRM_MP_JOB_SITES j ON d.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                   //+ " LEFT JOIN HRM_"
                   + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                   + (Sequence.HasValue ? " AND d.JOB_SITE_CMD_DTLS_SEQ = @Sequence" : "")
                   + (JobSiteCommand.HasValue ? " AND d.JOB_SITE_CMD_SEQ = @JobSiteCommand" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public JobSiteCommandDtlsInfo GetJobSiteCommandDtlsInfo(Nullable<int> Seq)
        {
            JobSiteCommandDtlsInfo Info = new JobSiteCommandDtlsInfo();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Seq", Seq);

                strSQL = "SELECT JOB_SITE_CMD_DTLS_SEQ, JOB_SITE_CMD_SEQ, JOB_SITE_SEQ"
                       + ", FROM_REF_NO, TO_REF_NO, FROM_JOB_SITE_NAME, TO_JOB_SITE_NAME"
                       + ", FROM_UPPER_SEQ, TO_UPPER_SEQ, FROM_UPPER_NAME, TO_UPPER_NAME"
                       + ", OLD_CMD_REF_NO, NEW_CMD_REF_NO, OLD_CMD_DATE, NEW_CMD_DATE"
                       + ", OLD_START_DATE"
                       + ", CANCEL, DATE_START, RECORD_STATUS"
                   + " FROM HRM_MP_JOB_SITE_COMMAND_DTLS"
                   + " WHERE RECORD_STATUS not like @DeleteRecord"
                       + " AND JOB_SITE_CMD_DTLS_SEQ = @Seq";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    Info.Seq = rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0);
                    Info.JobSiteCommandSeq = rdr.IsDBNull(1) ? (int?)null : rdr.GetInt32(1);
                    Info.JobSiteSeq = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
                    Info.OldRefNo = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
                    Info.NewRefNo = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
                    Info.OldName = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
                    Info.NewName = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
                    Info.OldUpperSeq = rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7);
                    Info.NewUpperSeq = rdr.IsDBNull(8) ? (int?)null : rdr.GetInt32(8);
                    Info.OldUpperName = rdr.IsDBNull(9) ? "" : rdr.GetString(9);
                    Info.NewUpperName = rdr.IsDBNull(10) ? "" : rdr.GetString(10);
                    Info.OldCmdRefNo = rdr.IsDBNull(11) ? "" : rdr.GetString(11);
                    Info.NewCmdRefNo = rdr.IsDBNull(12) ? "" : rdr.GetString(12);
                    Info.OldCmdDate = rdr.IsDBNull(13) ? (DateTime?)null : rdr.GetDateTime(13);
                    Info.NewCmdDate = rdr.IsDBNull(14) ? (DateTime?)null : rdr.GetDateTime(14);
                    Info.OldStartDate = rdr.IsDBNull(15) ? (DateTime?)null : rdr.GetDateTime(15);
                    Info.Cancel = rdr.IsDBNull(16) ? "" : rdr.GetString(16);
                    Info.StartDate = rdr.IsDBNull(17) ? (DateTime?)null : rdr.GetDateTime(17);
                }

                return Info;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<JobSiteCommandDtlsInfo> LoadJobSiteCommandsDtls(Nullable<int> JobSiteCommandSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<JobSiteCommandDtlsInfo> infoList = new List<JobSiteCommandDtlsInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@JobSiteCommandSeq", JobSiteCommandSeq);

                strSQL = "SELECT JOB_SITE_CMD_DTLS_SEQ, JOB_SITE_CMD_SEQ, JOB_SITE_SEQ"
                       + ", FROM_REF_NO, TO_REF_NO, FROM_JOB_SITE_NAME, TO_JOB_SITE_NAME"
                       + ", FROM_UPPER_SEQ, TO_UPPER_SEQ, FROM_UPPER_NAME, TO_UPPER_NAME"
                       + ", OLD_CMD_REF_NO, NEW_CMD_REF_NO, OLD_CMD_DATE, NEW_CMD_DATE"
                       + ", OLD_START_DATE, CANCEL, DATE_START, RECORD_STATUS"
                   + " FROM HRM_MP_JOB_SITE_COMMAND_DTLS"
                   + " WHERE RECORD_STATUS not like @DeleteRecord"
                   + (JobSiteCommandSeq.HasValue ? " AND JOB_SITE_CMD_SEQ = @JobSiteCommandSeq" : "") ;

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        JobSiteCommandDtlsInfo info = new JobSiteCommandDtlsInfo();
                        info.Seq = Convert.IsDBNull(dr[i]["JOB_SITE_CMD_DTLS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["JOB_SITE_CMD_DTLS_SEQ"].ToString());
                        info.JobSiteCommandSeq = Convert.IsDBNull(dr[i]["JOB_SITE_CMD_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["JOB_SITE_CMD_SEQ"].ToString());
                        info.JobSiteSeq = Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["JOB_SITE_SEQ"].ToString());
                        info.OldRefNo = Convert.IsDBNull(dr[i]["FROM_REF_NO"]) ? "" : (dr[i]["FROM_REF_NO"]).ToString();
                        info.NewRefNo = Convert.IsDBNull(dr[i]["TO_REF_NO"]) ? "" : (dr[i]["TO_REF_NO"]).ToString();
                        info.OldName = Convert.IsDBNull(dr[i]["FROM_JOB_SITE_NAME"]) ? "" : (dr[i]["FROM_JOB_SITE_NAME"]).ToString();
                        info.NewName = Convert.IsDBNull(dr[i]["TO_JOB_SITE_NAME"]) ? "" : (dr[i]["TO_JOB_SITE_NAME"]).ToString();
                        info.OldUpperSeq = Convert.IsDBNull(dr[i]["FROM_UPPER_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["FROM_UPPER_SEQ"].ToString());
                        info.NewUpperSeq = Convert.IsDBNull(dr[i]["TO_UPPER_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["TO_UPPER_SEQ"].ToString());
                        info.OldUpperName = Convert.IsDBNull(dr[i]["FROM_UPPER_NAME"]) ? "" : (dr[i]["FROM_UPPER_NAME"]).ToString();
                        info.NewUpperName = Convert.IsDBNull(dr[i]["TO_UPPER_NAME"]) ? "" : (dr[i]["TO_UPPER_NAME"]).ToString();
                        info.OldCmdRefNo = Convert.IsDBNull(dr[i]["OLD_CMD_REF_NO"]) ? "" : (dr[i]["OLD_CMD_REF_NO"]).ToString();
                        info.NewCmdRefNo = Convert.IsDBNull(dr[i]["NEW_CMD_REF_NO"]) ? "" : (dr[i]["NEW_CMD_REF_NO"]).ToString();
                        info.OldCmdDate = Convert.IsDBNull(dr[i]["OLD_CMD_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["OLD_CMD_DATE"].ToString());
                        info.NewCmdDate = Convert.IsDBNull(dr[i]["NEW_CMD_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["NEW_CMD_DATE"].ToString());
                        info.OldStartDate = Convert.IsDBNull(dr[i]["OLD_START_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["OLD_START_DATE"].ToString());
                        info.Cancel = Convert.IsDBNull(dr[i]["CANCEL"]) ? "" : (dr[i]["CANCEL"]).ToString();
                        info.ChkCancel = info.Cancel == "1";
                        info.StartDate = Convert.IsDBNull(dr[i]["DATE_START"]) ? (DateTime?)null : DateTime.Parse(dr[i]["DATE_START"].ToString());

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        } 

        public void Insert(JobSiteCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(20);

                dbManager.AddParameters(0, "@job_site_cmd_seq", Info.JobSiteCommandSeq);
                dbManager.AddParameters(1, "@job_site_seq", Info.JobSiteSeq);
                dbManager.AddParameters(2, "@from_ref_no", Info.OldRefNo);
                dbManager.AddParameters(3, "@to_ref_no", Info.NewRefNo);
                dbManager.AddParameters(4, "@from_job_site_name", Info.OldName);
                dbManager.AddParameters(5, "@to_job_site_name", Info.NewName);
                dbManager.AddParameters(6, "@from_upper_seq", Info.OldUpperSeq);
                dbManager.AddParameters(7, "@to_upper_seq", Info.NewUpperSeq);
                dbManager.AddParameters(8, "@from_upper_name", Info.OldUpperName);
                dbManager.AddParameters(9, "@to_upper_name", Info.NewUpperName);
                dbManager.AddParameters(10, "@old_cmd_ref_no", Info.OldCmdRefNo);
                dbManager.AddParameters(11, "@new_cmd_ref_no", Info.NewCmdRefNo);
                dbManager.AddParameters(12, "@old_cmd_date", Info.OldCmdDate);
                dbManager.AddParameters(13, "@new_cmd_date", Info.NewCmdDate);
                dbManager.AddParameters(14, "@old_start_date", Info.OldStartDate);
                dbManager.AddParameters(15, "@cancel", Info.Cancel);
                dbManager.AddParameters(16, "@date_start", Info.StartDate);
                dbManager.AddParameters(17, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(18, "@created_by", Info.CreatedBy);
                dbManager.AddParameters(19, "@created_when", DateTime.Today);

            strSQL = "INSERT INTO HRM_MP_JOB_SITE_COMMAND_DTLS"
                + "(JOB_SITE_CMD_SEQ, JOB_SITE_SEQ"
                    + ", FROM_REF_NO, TO_REF_NO, FROM_JOB_SITE_NAME, TO_JOB_SITE_NAME"
                    + ", FROM_UPPER_SEQ, TO_UPPER_SEQ, FROM_UPPER_NAME, TO_UPPER_NAME"
                    + ", OLD_CMD_REF_NO, NEW_CMD_REF_NO, OLD_CMD_DATE, NEW_CMD_DATE"
                    + ", OLD_START_DATE"
                    + ", CANCEL, DATE_START, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                + " VALUES (@job_site_cmd_seq, @job_site_seq"
                    + ", @from_ref_no, @to_ref_no, @from_job_site_name, @to_job_site_name"
                    + ", @from_upper_seq, @to_upper_seq, @from_upper_name, @to_upper_name"
                    + ", @old_cmd_ref_no, @new_cmd_ref_no, @old_cmd_date, @new_cmd_date"
                    + ", @old_start_date"
                    + ", @cancel, @date_start, @record_status, @created_by, @created_when"
                + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(JobSiteCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(20);

                dbManager.AddParameters(0, "@job_site_cmd_seq", Info.JobSiteCommandSeq);
                dbManager.AddParameters(1, "@job_site_seq", Info.JobSiteSeq);
                dbManager.AddParameters(2, "@from_ref_no", Info.OldRefNo);
                dbManager.AddParameters(3, "@to_ref_no", Info.NewRefNo);
                dbManager.AddParameters(4, "@from_job_site_name", Info.OldName);
                dbManager.AddParameters(5, "@to_job_site_name", Info.NewName);
                dbManager.AddParameters(6, "@from_upper_seq", Info.OldUpperSeq);
                dbManager.AddParameters(7, "@to_upper_seq", Info.NewUpperSeq);
                dbManager.AddParameters(8, "@from_upper_name", Info.OldUpperName);
                dbManager.AddParameters(9, "@to_upper_name", Info.NewUpperName);
                dbManager.AddParameters(10, "@old_cmd_ref_no", Info.OldCmdRefNo);
                dbManager.AddParameters(11, "@new_cmd_ref_no", Info.NewCmdRefNo);
                dbManager.AddParameters(12, "@old_cmd_date", Info.OldCmdDate);
                dbManager.AddParameters(13, "@new_cmd_date", Info.NewCmdDate);
                dbManager.AddParameters(14, "@old_start_date", Info.OldStartDate);
                dbManager.AddParameters(15, "@cancel", Info.Cancel);
                dbManager.AddParameters(16, "@seq", Info.Seq);
                dbManager.AddParameters(17, "@date_start", Info.StartDate);
                dbManager.AddParameters(18, "@updated_by", Info.UpdatedBy);
                dbManager.AddParameters(19, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_JOB_SITE_COMMAND_DTLS"
                    + " SET JOB_SITE_CMD_SEQ = @job_site_cmd_seq"
                        + ", JOB_SITE_SEQ = @job_site_seq"
                        + ", FROM_REF_NO = @from_ref_no"
                        + ", TO_REF_NO = @to_ref_no"
                        + ", FROM_JOB_SITE_NAME = @from_job_site_name"
                        + ", TO_JOB_SITE_NAME = @to_job_site_name"
                        + ", FROM_UPPER_SEQ = @from_upper_seq"
                        + ", TO_UPPER_SEQ = @to_upper_seq"
                        + ", FROM_UPPER_NAME = @from_upper_name"
                        + ", TO_UPPER_NAME = @to_upper_name"
                        + ", OLD_CMD_REF_NO = @old_cmd_ref_no"
                        + ", NEW_CMD_REF_NO = @new_cmd_ref_no"
                        + ", OLD_CMD_DATE = @old_cmd_date"
                        + ", NEW_CMD_DATE = @new_cmd_date"
                        + ", OLD_START_DATE = @old_start_date"
                        + ", DATE_START = @date_start"
                        + ", CANCEL = @cancel"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE JOB_SITE_CMD_DTLS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(JobSiteCommandDtlsInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", Info.UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_JOB_SITE_COMMAND_DTLS"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE JOB_SITE_CMD_DTLS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
